// This File is part of the "nyaqt" Project
// 
// Copyright  2009 sometmes@gmail.com
// All rights reserved
// 
// This library is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published
// by the Free Software Foundation; either version 2.1 of the License, or
// (at your option) any later version.
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
// 
// You should have received a copy of the GNU General Public License
// along with this program.  If not, see <http://www.gnu.org/licenses/>.



using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using nyaqt.Plugins;
using System.Globalization;
using System.Collections.Specialized;
using System.Data.Common;
using System.Text.RegularExpressions;
using System.IO;

namespace nyaqt
{
    class SybaseConnectionProperties : Type1ConnectionProperties
    {
        //public string UserName
        //{
        //    get
        //    {
        //        return GetBuilderValue(_connectionInfo.ConnectionStringBuilder, "user ID", "userid", "uid") as string;
        //    }
        //}

        //protected object GetBuilderValue(DbConnectionStringBuilder builder, params string[] equivalentKeys)
        //{
        //    foreach (string key in equivalentKeys)
        //        if (builder.ContainsKey(key))
        //            return builder[key];
        //    return null;
        //}

        //protected string SetBuilderValue(DbConnectionStringBuilder builder, object value, params string[] equivalentKeys)
        //{
        //    foreach (string key in equivalentKeys)
        //    {
        //        if (builder.ContainsKey(key))
        //        {
        //            builder[key] = value;
        //            return key;
        //        }
        //    }
        //    string key2 = equivalentKeys[0];
        //    builder[key2] = value;
        //    return key2;
        //}

        virtual public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabases()
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            IDbCommand cmd = _cn.CreateCommand();
            cmd.CommandText = "sp_databases";
            cmd.Connection = _cn;
            IDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                DatabaseEntityInfo e = new DatabaseEntityInfo();
                e.DatabaseName = r["database_name"] as string;
                e.DisplayName = e.DatabaseName;
                ret.Add(e);
            }
            r.Close();
            ret.Sort();
            return ret;
        }

        virtual protected List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseTablesEx(string database, string type)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            IDbCommand cmd = _cn.CreateCommand();
            cmd.CommandText = "use " + database + "  exec sp_tables @table_type = \"'" + type + "'\" ";
            cmd.Connection = _cn;
            IDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                DatabaseEntityInfo e = new DatabaseEntityInfo();
                e.DatabaseName = r["table_qualifier"] as string;
                e.TableNamespace = r["table_owner"] as string;
                e.TableName = r["table_name"] as string;
                e.TableType = type;
                e.TableDescription = r["remarks"] as string;
                e.DisplayName = e.TableName;
                if (e.TableNamespace != "dbo")
                    e.DisplayName += "(" + e.TableNamespace + ")";
                e.ClipboardText1 = e.TableName;
                e.ClipboardText2 = string.Format("[{0}].[{1}]", e.TableNamespace, e.TableName);
                ret.Add(e);
            }
            r.Close();
            ret.Sort();
            return ret;
        }

        public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseTables(string database)
        {
            return GetDatabaseTablesEx(database, "TABLE");
        }

        public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseViews(string database)
        {
            return GetDatabaseTablesEx(database, "VIEW");
        }

        public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseSystemTables(string database)
        {
            return GetDatabaseTablesEx(database, "SYSTEM TABLE");
        }

        virtual public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseStoredProcedures(string database)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            IDbCommand cmd = _cn.CreateCommand();
            cmd.CommandText = "use " + database + "  exec sp_stored_procedures ";
            cmd.Connection = _cn;
            IDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                DatabaseEntityInfo e = new DatabaseEntityInfo();
                e.DatabaseName = r["procedure_qualifier"] as string;
                e.TableNamespace = r["procedure_owner"] as string;
                e.TableName = r["procedure_name"] as string;
                e.TableType = "STORED PROC";
                e.TableDescription = r["remarks"] as string;
                e.DisplayName = e.TableName;
                if (e.TableNamespace != "dbo")
                    e.DisplayName += "(" + e.TableNamespace + ")";
                ret.Add(e);
            }
            r.Close();
            ret.Sort();
            return ret;
        }

        protected string FormatColumnType(IDataRecord col, bool withMode)
        {
            string type = "";

            object o = col["char_octet_length"];
            int char_octet_length = 0;
            if (o != DBNull.Value)
                char_octet_length = (int)o;

            string type_name = col["type_name"] as string;
            bool NULL = "YES".Equals(col["is_nullable"] as string, StringComparison.CurrentCultureIgnoreCase);
            if (char_octet_length == 0)
                type += type_name;
            else
                type += type_name + "(" + char_octet_length + ")";

            if (NULL)
                type += " null";
            else
                type += " not null";

            if (withMode)
                type = col["mode"] as string + " " + type;

            return type;
        }

        virtual public List<DatabaseEntityInfo> GetDatabaseTableColumns(string database, string tableNamespace, string table)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            List<string> keys = new List<string>();
            IDbCommand cmd = _cn.CreateCommand();
            cmd.CommandText = "use " + database + "  exec sp_pkeys @table_owner = '" + tableNamespace + "', @table_name = '" + table + "' ";
            cmd.Connection = _cn;
            IDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                keys.Add(r["column_name"] as string);
            }
            r.Close();

            cmd = _cn.CreateCommand();
            cmd.CommandText = "use " + database + "  exec sp_columns @table_owner = '" + tableNamespace + "', @table_name = '" + table + "' ";
            cmd.Connection = _cn;
            r = cmd.ExecuteReader();
            while (r.Read())
            {
                DatabaseEntityInfo e = new DatabaseEntityInfo();
                e.DatabaseName = r["table_qualifier"] as string;
                e.TableNamespace = r["table_owner"] as string;
                e.TableName = r["table_name"] as string;
                e.ColumnName = r["column_name"] as string;
                e.TableType = "COLUMN";
                e.TableDescription = r["remarks"] as string;
                if (keys.Contains(e.ColumnName))
                    e.IsPrimaryKey = true;
                e.IsNullable = "YES".Equals(r["is_nullable"] as string, StringComparison.CurrentCultureIgnoreCase);
                e.DisplayName = e.ColumnName + " : " + FormatColumnType(r, false);
                e.ClipboardText1 = e.ColumnName;
                e.ClipboardText2 = e.ColumnName + "  " + FormatColumnType(r, false);
                ret.Add(e);
            }
            r.Close();
            return ret;
        }

        virtual protected void GetDatabaseTableRelations(List<DatabaseEntityInfo> list, string database, string tableNamespace, string table, bool outgoing)
        {
            IDbCommand cmd = null;
            string table_column, tableowner_column, related_column, relatedowner_column, relateddb_column;
            if (!outgoing)
            {
                table_column = "pktable_name";
                tableowner_column = "pktable_owner";
                related_column = "fktable_name";
                relatedowner_column = "fktable_owner";
                relateddb_column = "fktable_qualifier";
            }
            else
            {
                table_column = "fktable_name";
                tableowner_column = "fktable_owner";
                related_column = "pktable_name";
                relatedowner_column = "pktable_owner";
                relateddb_column = "pktable_qualifier";
            }

            cmd = _cn.CreateCommand();
            cmd.CommandText = "use " + database + "  exec sp_fkeys @" + tableowner_column + " = '" + tableNamespace + "', @" + table_column + " = '" + table + "' ";
            cmd.Connection = _cn;

            IDataReader r = cmd.ExecuteReader();
            string fktable_name = "";
            while (r.Read())
            {
                if (fktable_name != r[related_column] as string)
                {
                    fktable_name = r[related_column] as string;

                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DatabaseName = r[relateddb_column] as string;
                    e.TableNamespace = r[tableowner_column] as string;
                    e.TableName = r[table_column] as string;
                    e.RelatedTable = r[related_column] as string;
                    e.RelatedTableNamespace = r[relatedowner_column] as string;
                    e.IsOutGoing = outgoing;
                    e.TableType = "RELATION";
                    e.DisplayName = fktable_name;
                    if (e.TableNamespace != "dbo")
                        e.DisplayName += "(" + e.TableNamespace + ")";
                    list.Add(e);
                }
            }
            r.Close();
        }

        public List<DatabaseEntityInfo> GetDatabaseTableRelations(string database, string tableNamespace, string table)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            GetDatabaseTableRelations(ret, database, tableNamespace, table, false);
            GetDatabaseTableRelations(ret, database, tableNamespace, table, true);
            ret.Sort();
            return ret;
        }

        virtual public List<DatabaseEntityInfo> GetDatabaseTableRelationColumns(string database, string tableNamespace, string table, string relatedTableNamespace, string relatedTable)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            IDbCommand cmd = _cn.CreateCommand();
            cmd.CommandText = "use " + database + "  exec sp_fkeys @pktable_owner = '" + relatedTableNamespace + "', @pktable_name = '" + relatedTable + "', @fktable_owner = '" + tableNamespace + "', @fktable_name = '" + table + "' ";
            cmd.Connection = _cn;
            IDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                DatabaseEntityInfo e = new DatabaseEntityInfo();
                e.DatabaseName = r["fktable_qualifier"] as string;
                e.TableNamespace = r["fktable_owner"] as string;
                e.TableName = r["fktable_name"] as string;
                e.RelatedTable = r["pktable_name"] as string;
                e.RelatedTableNamespace = r["pktable_owner"] as string;
                e.ColumnName = r["fkcolumn_name"] as string;
                e.RelatedColumnName = r["pkcolumn_name"] as string;
                e.TableType = "RELATION COLUMN";
                if (e.IsOutGoing)
                    e.DisplayName = e.RelatedColumnName + " = " + e.ColumnName;
                else
                    e.DisplayName = e.ColumnName + " = " + e.RelatedColumnName;
                ret.Add(e);
            }
            r.Close();
            return ret;
        }

        virtual public List<DatabaseEntityInfo> GetDatabaseStoredProcedureParameters(string database, string tableNamespace, string table)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            IDbCommand cmd = _cn.CreateCommand();
            cmd.CommandText = "use " + database + "  exec sp_sproc_columns @procedure_owner = '" + tableNamespace + "', @procedure_name = '" + table + "' ";
            cmd.Connection = _cn;
            IDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                DatabaseEntityInfo e = new DatabaseEntityInfo();
                e.DatabaseName = r["procedure_qualifier"] as string;
                e.TableNamespace = r["procedure_owner"] as string;
                e.TableName = r["procedure_name"] as string;
                e.ColumnName = r["column_name"] as string;
                e.TableType = "COLUMN";
                e.TableDescription = r["remarks"] as string;
                e.DisplayName = e.ColumnName + " : " + FormatColumnType(r, true);
                ret.Add(e);
            }
            r.Close();
            return ret;
        }

        //public string ConnectionStringPasswordKey
        //{
        //    get { return "Password"; }
        //}

        //protected int[] strings;
        //protected int[] dates;
        //protected int[] numfixed;
        //protected int[] numvar;

        //protected string FormatDbType(IDataParameter param)
        //{
        //    DbType dbType = param.DbType;
        //    if (-1 != Array.IndexOf(strings, dbType))
        //    {
        //        string s = "" + (param.Value as string);
        //        return "varchar(" + s.Length + ")";
        //    }
        //    if (-1 != Array.IndexOf(dates, dbType))
        //    {
        //        return "datetime";
        //    }
        //    if (-1 != Array.IndexOf(numfixed, dbType))
        //    {
        //        return "int";
        //    }
        //    if (-1 != Array.IndexOf(numvar, dbType))
        //    {
        //        int scale;
        //        int precision;
        //        GetScaleAndPrecision(out scale, out precision, param);
        //        string d = string.Format(CultureInfo.InvariantCulture, "{F}", param.Value);
        //        return "numeric(" + scale + "," + precision + ")";
        //    }
        //    else
        //        return "�" + dbType.ToString() + "?";
        //}

        public void GetConnectionStringKeywordMap(Dictionary<KnownNyaqtConnectionKeys, string> knownKeywords, Dictionary<string, string> keywordMap)
        {
            knownKeywords[KnownNyaqtConnectionKeys.Server] = "Data Source";
            knownKeywords[KnownNyaqtConnectionKeys.Database] = "Initial Catalog";
            knownKeywords[KnownNyaqtConnectionKeys.Login] = "User ID";
            knownKeywords[KnownNyaqtConnectionKeys.Password] = "Password";

            keywordMap["APP"] = "Application Name";
            keywordMap["APPLICATION NAME"] = "Application Name";
            keywordMap["ATTACHDBFILENAME"] = "AttachDbFilename";
            keywordMap["EXTENDED PROPERTIES"] = "Extended Properties";
            keywordMap["INITIAL FILE NAME"] = "Initial File Name";
            keywordMap["TIMEOUT"] = "Connect Timeout";
            keywordMap["CONNECT TIMEOUT"] = "Connect Timeout";
            keywordMap["CONNECTION TIMEOUT"] = "Connect Timeout";
            keywordMap["CONNECTION RESET"] = "Connection Reset";
            keywordMap["LANGUAGE"] = "Current Language";
            keywordMap["CURRENT LANGUAGE"] = "Current Language";
            keywordMap["DATA SOURCE"] = knownKeywords[KnownNyaqtConnectionKeys.Server];
            keywordMap["DATASOURCE"] = knownKeywords[KnownNyaqtConnectionKeys.Server];
            keywordMap["SERVER"] = knownKeywords[KnownNyaqtConnectionKeys.Server];
            keywordMap["SERVER NAME"] = knownKeywords[KnownNyaqtConnectionKeys.Server];
            keywordMap["ADDRESS"] = knownKeywords[KnownNyaqtConnectionKeys.Server];
            keywordMap["ADDR"] = knownKeywords[KnownNyaqtConnectionKeys.Server];
            keywordMap["NETWORK ADDRESS"] = knownKeywords[KnownNyaqtConnectionKeys.Server];
            keywordMap["ENCRYPT"] = "Encrypt";
            keywordMap["ENLIST"] = "Enlist";
            keywordMap["INITIAL CATALOG"] = knownKeywords[KnownNyaqtConnectionKeys.Database];
            keywordMap["DATABASE"] = knownKeywords[KnownNyaqtConnectionKeys.Database];
            keywordMap["INTEGRATED SECURITY"] = "Integrated Security";
            keywordMap["TRUSTED_CONNECTION"] = "Integrated Security";
            keywordMap["MAX POOL SIZE"] = "Max Pool Size";
            keywordMap["MIN POOL SIZE"] = "Min Pool Size";
            keywordMap["ASYNCHRONOUS PROCESSING"] = "Asynchronous Processing";
            keywordMap["ASYNC"] = "Async";
            keywordMap["PACKET SIZE"] = "Packet Size";
            keywordMap["PASSWORD"] = knownKeywords[KnownNyaqtConnectionKeys.Password];
            keywordMap["PWD"] = knownKeywords[KnownNyaqtConnectionKeys.Password];
            keywordMap["PERSISTSECURITYINFO"] = "Persist Security Info";
            keywordMap["PERSIST SECURITY INFO"] = "Persist Security Info";
            keywordMap["POOLING"] = "Pooling";
            keywordMap["UID"] = knownKeywords[KnownNyaqtConnectionKeys.Login];
            keywordMap["USER"] = knownKeywords[KnownNyaqtConnectionKeys.Login];
            keywordMap["USER ID"] = knownKeywords[KnownNyaqtConnectionKeys.Login];
            keywordMap["WSID"] = "Workstation ID";
            keywordMap["WORKSTATION ID"] = "Workstation ID";
        }

        Regex _sqlIniRegex = new Regex(
@"	^\[(?<SERVER>\w+)\]
	(\r\n)*\n*
	^query=(?<PROTOCOL>\w*),(?<IP>[\w.]*),(?<PORT>\w*)"
            , RegexOptions.ExplicitCapture | RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline);

        const string _sybaseEnv = "SYBASE";
        const string _iniPath = "%" + _sybaseEnv + @"%\INI\sql.ini";
        const string _notloaded = "##notloaded##";
        string ResolveIniPath()
        {
            string sybPath = Environment.GetEnvironmentVariable(_sybaseEnv);
            if (string.IsNullOrEmpty(sybPath))
                sybPath = @"C:\SYBASE\INI";
            string iniPath = _iniPath.Replace("%" + _sybaseEnv + "%", sybPath);
            return iniPath;
        }

        public List<ServerInfo> GetServerList()
        {
            List<ServerInfo> list = new List<ServerInfo>();
            string iniText = File.ReadAllText(ResolveIniPath());
            MatchCollection servers = _sqlIniRegex.Matches(iniText);
            foreach (Match server in servers)
            {
                ServerInfo item = new ServerInfo();
                item.DisplayName = server.Groups["SERVER"].Value;
                item.ServerAddress = server.Groups["IP"].Value + "," + server.Groups["PORT"].Value;
                list.Add(item);
            }
            return list;
        }

        public bool ServerAddressEqual(string addr1, string addr2)
        {
            string[] parts = addr1.Split(',');
            string server = "";
            string port = "5000";
            if (parts.Length > 0)
                server = parts[0];
            if (parts.Length > 1)
                port = parts[1];

            parts = addr2.Split(',');
            string server2 = "";
            string port2 = "5000";
            if (parts.Length > 0)
                server2 = parts[0];
            if (parts.Length > 1)
                port2 = parts[1];

            return (server == server2 && port == port2);
        }
    }

}
































